﻿using System;
using System.Data;
using System.Configuration;
using System.Data.SqlClient;

namespace DAL
{
    public class StoredProcedures
    {
        #region Common method
        private MainDB _db;

        public StoredProcedures(MainDB db)
        {
            _db = db;
        }

        public MainDB Database
        {
            get { return _db; }
        }

        protected DataTable CreateDataTable(IDbCommand command)
        {
            return _db.CreateDataTable(command);
        }
        #endregion

        #region Category
        public DataTable Category_Get(int Category_ID)
        {
            IDbCommand cmd = _db.CreateCommand("Category_Get", true);
            _db.AddParameter(cmd, "Category_ID", DbType.Int32, Category_ID);
            return CreateDataTable(cmd);
        }
        public void Category_Update(int Category_ID, string Category_Name, string Category_Icon, string Category_DisplayUrl)
        {
            IDbCommand cmd = _db.CreateCommand("Category_Update", true);
            _db.AddParameter(cmd, "Category_ID", DbType.Int32, Category_ID);
            _db.AddParameter(cmd, "Category_Name", DbType.String, Category_Name);
            _db.AddParameter(cmd, "Category_Icon", DbType.String, Category_Icon);
            _db.AddParameter(cmd, "Category_DisplayUrl", DbType.String, Category_DisplayUrl);
            CreateDataTable(cmd);
        }
        public DataTable Category_GetAll()
        {
            IDbCommand cmd = _db.CreateCommand("Category_GetAll", true);
            return CreateDataTable(cmd);
        }
        public void Category_UpdateIcon(int Category_ID, string Category_Icon)
        {
            IDbCommand cmd = _db.CreateCommand("Category_UpdateIcon", true);
            _db.AddParameter(cmd, "Category_ID", DbType.Int32, Category_ID);
            _db.AddParameter(cmd, "Category_Icon", DbType.String, Category_Icon);
            CreateDataTable(cmd);
        }
        #endregion

        #region Image
        public DataTable Image_GetByNewsID(int News_ID)
        {
            IDbCommand cmd = _db.CreateCommand("Image_GetByNewsID", true);
            _db.AddParameter(cmd, "News_ID", DbType.Int32, News_ID);
            return CreateDataTable(cmd);
        }
        public DataTable Image_Add(string Image_Url, int News_ID)
        {
            IDbCommand cmd = _db.CreateCommand("Image_Add", true);
            _db.AddParameter(cmd, "Image_Url", DbType.String, Image_Url);
            _db.AddParameter(cmd, "News_ID", DbType.Int32, News_ID);
            return CreateDataTable(cmd);
        }
        public void Image_Delete(int Image_ID)
        {
            IDbCommand cmd = _db.CreateCommand("Image_Delete", true);
            _db.AddParameter(cmd, "Image_ID", DbType.Int32, Image_ID);
            CreateDataTable(cmd);
        }
        public void Image_DeleteByNewsID(int News_ID)
        {
            IDbCommand cmd = _db.CreateCommand("Image_DeleteByNewsID", true);
            _db.AddParameter(cmd, "News_ID", DbType.Int32, News_ID);
            CreateDataTable(cmd);
        }
        #endregion

        #region News
        public DataTable News_GetByCategory(int Category_ID)
        {
            IDbCommand cmd = _db.CreateCommand("News_GetByCategory", true);
            _db.AddParameter(cmd, "Category_ID", DbType.Int32, Category_ID);
            return CreateDataTable(cmd);
        }
        public DataTable News_AddDump(string News_Title)
        {
            IDbCommand cmd = _db.CreateCommand("News_AddDump", true);
            _db.AddParameter(cmd, "News_Title", DbType.String, News_Title);
            return CreateDataTable(cmd);
        }
        public DataTable News_GetByNewsID(int News_ID)
        {
            IDbCommand cmd = _db.CreateCommand("News_GetByNewsID", true);
            _db.AddParameter(cmd, "News_ID", DbType.Int32, News_ID);
            return CreateDataTable(cmd);
        }
        public void News_Update(int News_ID, int Category_ID, string News_Title, string Photo, string Stylist, string Makeup, string Model,
            string Costume, string Location, int News_Status, string News_Photo)
        {
            IDbCommand cmd = _db.CreateCommand("News_Update", true);
            _db.AddParameter(cmd, "News_ID", DbType.Int32, News_ID);
            _db.AddParameter(cmd, "Category_ID", DbType.Int32, Category_ID);
            _db.AddParameter(cmd, "News_Title", DbType.String, News_Title);
            _db.AddParameter(cmd, "Photo", DbType.String, Photo);
            _db.AddParameter(cmd, "Stylist", DbType.String, Stylist);
            _db.AddParameter(cmd, "Makeup", DbType.String, Makeup);
            _db.AddParameter(cmd, "Model", DbType.String, Model);
            _db.AddParameter(cmd, "Costume", DbType.String, Costume);
            _db.AddParameter(cmd, "Location", DbType.String, Location);
            _db.AddParameter(cmd, "News_Status", DbType.Int32, News_Status);
            _db.AddParameter(cmd, "News_Photo", DbType.String, News_Photo);
            CreateDataTable(cmd);
        }
        public void News_UpdateStatus(int News_ID, int News_Status)
        {
            IDbCommand cmd = _db.CreateCommand("News_UpdateStatus", true);
            _db.AddParameter(cmd, "News_ID", DbType.Int32, News_ID);
            _db.AddParameter(cmd, "News_Status", DbType.Int32, News_Status);
            CreateDataTable(cmd);
        }
        public DataTable News_GetTopAll(int Top)
        {
            IDbCommand cmd = _db.CreateCommand("News_GetTopAll", true);
            _db.AddParameter(cmd, "Top", DbType.Int32, Top);
            return CreateDataTable(cmd);
        }
        public DataTable News_GetPaging(int Category_ID, int PageIndex, int PageSize)
        {
            IDbCommand cmd = _db.CreateCommand("News_GetPaging", true);
            _db.AddParameter(cmd, "Category_ID", DbType.Int32, Category_ID);
            _db.AddParameter(cmd, "PageIndex", DbType.Int32, PageIndex);
            _db.AddParameter(cmd, "PageSize", DbType.Int32, PageSize);
            return CreateDataTable(cmd);
        }
        public DataTable News_Count(int Category_ID)
        {
            IDbCommand cmd = _db.CreateCommand("News_Count", true);
            _db.AddParameter(cmd, "Category_ID", DbType.Int32, Category_ID);
            return CreateDataTable(cmd);
        }
        public DataTable News_GetHighView(int Top)
        {
            IDbCommand cmd = _db.CreateCommand("News_GetHighView", true);
            _db.AddParameter(cmd, "Top", DbType.Int32, Top);
            return CreateDataTable(cmd);
        }
        #endregion

        #region Settings
        public DataTable Settings_Get(string Key)
        {
            IDbCommand cmd = _db.CreateCommand("Settings_Get", true);
            _db.AddParameter(cmd, "Key", DbType.String, Key);
            return CreateDataTable(cmd);
        }
        public DataTable Settings_GetAll()
        {
            IDbCommand cmd = _db.CreateCommand("Settings_GetAll", true);
            return CreateDataTable(cmd);
        }
        public void Settings_Update(string Key, string Value)
        {
            IDbCommand cmd = _db.CreateCommand("Settings_Update", true);
            _db.AddParameter(cmd, "Key", DbType.String, Key);
            _db.AddParameter(cmd, "Value", DbType.String, Value);
            CreateDataTable(cmd);
        }
        #endregion
    }
}